﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.SqlClient;
using Common;
using CNCSoft.ErpApps.TMS.myclass;
using System.IO;
using System.Drawing.Imaging;

namespace CNCSoft.ErpApps.TMS.longGiaoVu
{
    public partial class frmQuanLyHocSinh : DevExpress.XtraEditors.XtraForm
    {
        private SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        private DataSet ds = new DataSet();
        private CommonClass mine = new CommonClass();

        private bool signTableEnquiry = false;
        private bool signTableEnquiryByNum = false;
        private bool signTablePhuHuynhByEnquiry = false;
        private bool signTablePhuHuynhByMaPhuHuynh = false;

        private string _enquirynumber = "";
        private int _maphuhuynh = 0;
        private string _destfile = "";

        public frmQuanLyHocSinh()
        {
            InitializeComponent();
        }

        private void frmQuanLyHocSinh_Load(object sender, EventArgs e)
        {
            try
            {
                LoadControl();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void searchLookUpEditHocSinh_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                _enquirynumber = searchLookUpEditHocSinh.EditValue.ToString();
                LoadEnquiryByEnNum(_enquirynumber);
                string HoLot = ds.Tables["TableEnquiryByNum"].Rows[0][0].ToString();
                string Ten = ds.Tables["TableEnquiryByNum"].Rows[0][1].ToString();
                lblHoLotVaTen.Text = HoLot + " " + Ten;
                lblTenHocSinh.Text = HoLot + " " + Ten;
                lblTenHocSinhHinh.Text = HoLot + " " + Ten;
                LoadPhuHuynhByEnquiry(_enquirynumber);
                RefreshData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridControlPhuHuynh_Click(object sender, EventArgs e)
        {
            try
            {
                LoadPhuHuynhDetail(_maphuhuynh);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnAddPhuHuynh_Click(object sender, EventArgs e)
        {
            Validation();
            string tenphuhuynh = txtTenPhuHuynh.Text;
            string phone = txtPhone.Text;
            string maquanhe = cboQuanHe2.SelectedValue.ToString();
            
            AddPhuHuynh(tenphuhuynh, phone, maquanhe, _enquirynumber);
        }

        private void btnEditPhuHuynh_Click(object sender, EventArgs e)
        {
            Validation();
            if (_maphuhuynh != 0)
            {
                int maphuhuynh = _maphuhuynh;
                string tenphuhuynh = txtTenPhuHuynh.Text;
                string phone = txtPhone.Text;
                string maquanhe = cboQuanHe2.SelectedValue.ToString();
                EditPhuHuynh(maphuhuynh, tenphuhuynh, phone, maquanhe);
            }
        }

        private void btnXoaPhuHuynh_Click(object sender, EventArgs e)
        {
            Validate();
            if(_maphuhuynh != 0)
                XoaPhuHuynh(_maphuhuynh);
        }

        private void btnChoosePic_Click(object sender, EventArgs e)
        {
            try
            {
                UpLoadImage();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLuuHinh_Click(object sender, EventArgs e)
        {
            try
            {
                SimpleCopyFile();
                if (txtMaHinh.Text == "" || txtTenHinh.Text == "" || txtLinkHinh.Text == "")
                {
                    MessageBox.Show("Tất cả thông tin không được để trống!");
                    return;
                }
                string mahinh = txtMaHinh.Text;
                string tenhinh = txtTenHinh.Text;
                string linkhinh = _destfile;

                InsertImage(mahinh, tenhinh, linkhinh, _enquirynumber);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLamLai_Click(object sender, EventArgs e)
        {
            try
            {
                txtMaHinh.Text = "";
                txtTenHinh.Text = "";
                txtLinkHinh.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region "Coding"
        private void LoadControl()
        {
            try
            {
                LoadEnquiry();
                LoadQuanHe();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadQuanHe()
        {
            try
            {
                string sql =
                    @"SELECT * FROM DM_QUANHE";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableQuanHe", "MaQuanHe", "TenQuanHe", cboQuanHe2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadEnquiry()
        {
            try
            {
                if (signTableEnquiry)
                    ds.Tables["TableEnquiry"].Clear();
                string sql =
                    @"SELECT EnquiryNumber, HoLot, Ten FROM Enquiry";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableEnquiry", "EnquiryNumber", "Ten", searchLookUpEditHocSinh);
                signTableEnquiry = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadEnquiryByEnNum(string enquirynumber)
        {
            try
            {
                if (signTableEnquiryByNum)
                    ds.Tables["TableEnquiryByNum"].Clear();
                string sql =
                    @"SELECT HoLot, Ten FROM Enquiry WHERE EnquiryNumber = '" + enquirynumber + "'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableEnquiryByNum");
                signTableEnquiryByNum = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadPhuHuynhByEnquiry(string enquirynumber)
        {
            try
            {
                if (signTablePhuHuynhByEnquiry)
                    ds.Tables["TablePhuHuynhByEnquiry"].Rows.Clear();
                string sql =
                    @"SELECT ph.MaPhuHuynh AS N'Mã phụ huynh', ph.TenPhuHuynh AS N'Tên Phụ Huynh', dmqh.TenQuanHe AS N'Quan hệ', 
                    ph.Phone AS N'Điện thoại', enq.EnquiryNumber AS N'Mã tư vấn'
                    FROM Enquiry enq LEFT OUTER JOIN PHUHUYNH ph ON enq.EnquiryNumber = ph.EnquiryNumber
                    LEFT OUTER JOIN DM_QUANHE dmqh ON dmqh.MaQuanHe = ph.MaQuanHe
                    WHERE enq.EnquiryNumber = '" + enquirynumber + "'";
                mine.ExecDataAdapter(sql, conn, ds, "TablePhuHuynhByEnquiry", gridControlPhuHuynh);
                signTablePhuHuynhByEnquiry = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadPhuHuynhByMaPhuHuynh(int maphuhuynh)
        {
            try
            {
                if (signTablePhuHuynhByMaPhuHuynh)
                    ds.Tables["TablePhuHuynhByMaPhuHuynh"].Clear();
                string sql =
                    @"SELECT * FROM PHUHUYNH WHERE MaPhuHuynh = " + maphuhuynh;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TablePhuHuynhByMaPhuHuynh");
                signTablePhuHuynhByMaPhuHuynh = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadPhuHuynhDetail(int maphuhuynh)
        {
            try
            {
                string strField = null;
                string[] listmaph = mine.ChoosedValue(gridViewPhuHuynh, strField);
                
                if (listmaph.Length > 0)
                {
                    if(listmaph[0] != "" && listmaph[0] != null)
                        _maphuhuynh = Convert.ToInt32(listmaph[0]);
                }
                if (_maphuhuynh != 0)
                {
                    LoadPhuHuynhByMaPhuHuynh(_maphuhuynh);
                    string tenphuhuynh = ds.Tables["TablePhuHuynhByMaPhuHuynh"].Rows[0][1].ToString();
                    string sodt = ds.Tables["TablePhuHuynhByMaPhuHuynh"].Rows[0][2].ToString();
                    string maquanhe = ds.Tables["TablePhuHuynhByMaPhuHuynh"].Rows[0][3].ToString();
                    txtTenPhuHuynh.Text = tenphuhuynh;
                    txtPhone.Text = sodt;
                    cboQuanHe2.SelectedValue = maquanhe;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void EditPhuHuynh(int maphuhuynh, string tenphuhuynh, string phone, string maquanhe)
        {
            try
            {
                int i = 0;
                string sql =
                    @"UPDATE PHUHUYNH SET TenPhuHuynh = @tenphuhuynh, Phone = @phone, MaQuanHe = @maquanhe
                    WHERE MaPhuHuynh = @maphuhuynh";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@maphuhuynh", maphuhuynh);
                cmd.Parameters.AddWithValue("@tenphuhuynh", tenphuhuynh);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@maquanhe", maquanhe);
                try
                {
                    conn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                if (i > 0)
                {
                    MessageBox.Show("Chỉnh sửa phụ huynh thành công!");
                    LoadPhuHuynhByEnquiry(_enquirynumber);
                }
                else
                {
                    MessageBox.Show("Ko thể chỉnh sửa phụ huynh");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void AddPhuHuynh(string tenphuhuynh, string phone, string maquanhe, string enquirynumber)
        {
            try
            {
                int i = 0;
                string sql =
                    @"INSERT INTO PHUHUYNH(TenPhuHuynh, Phone, MaQuanHe, EnquiryNumber)
                    VALUES(@tenphuhuynh, @phone, @maquanhe, @enquirynumber)";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@tenphuhuynh", tenphuhuynh);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@maquanhe", maquanhe);
                cmd.Parameters.AddWithValue("@enquirynumber", enquirynumber);
                try
                {
                    conn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                if (i > 0)
                {
                    MessageBox.Show("Thêm giáo viên thành công!");
                    LoadPhuHuynhByEnquiry(_enquirynumber);
                }
                else
                {
                    MessageBox.Show("Thất bại!!!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void XoaPhuHuynh(int maphuhuynh)
        {
            try
            {
                int i = 0;
                string sql =
                    @"DELETE PHUHUYNH WHERE MaPhuHuynh = @maphuhuynh";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@maphuhuynh", maphuhuynh);
                try
                {
                    conn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                if (i > 0)
                {
                    MessageBox.Show("Xóa phụ huynh thành công!!!");
                    LoadPhuHuynhByEnquiry(_enquirynumber);
                }
                else
                    MessageBox.Show("Xóa thất bại");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Validation()
        {
            try
            {
                if(_enquirynumber == "")
                {
                    MessageBox.Show("Bạn chưa chọn học sinh");
                    return;
                }
                if (txtTenPhuHuynh.Text == "")
                {
                    MessageBox.Show("Bạn chưa có phụ huynh");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void RefreshData()
        {
            txtTenPhuHuynh.Text = "";
            txtPhone.Text = "";
            cboQuanHe2.SelectedIndex = 0;
        }

        //-------------tag2---------------------------------------------------------------------------------------
        //Browse an Image from system hard disk and show the image
        //in PictureBox control
        private void UpLoadImage()
        {
            try
            {
                //Create an object of OpenFileDialog to browse Image
                OpenFileDialog open = new OpenFileDialog();
                //Filter the dialogbox so that user can select only some specific file from hard disk
                open.Filter = "Image Files (*.jpg, *.jpeg, *.gif, *.bmp) |*.jpg";
                //If user selected any file then do the following
                if (open.ShowDialog() == DialogResult.OK)
                {
                    txtLinkHinh.Text = open.FileName;
                    //Create an object of Image class and assign the image name
                    Image img = new Bitmap(open.FileName);
                    //Show the image in the PictureBox control
                    pictureEdit1.Image = img.GetThumbnailImage(400, 400, null, IntPtr.Zero);
                    open.RestoreDirectory = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void SimpleCopyFile()
        {
            try
            {
                string sourcePath = txtLinkHinh.Text;
                string targetPath = Environment.CurrentDirectory + @"\Pic";

                string file = sourcePath.Substring(sourcePath.LastIndexOf("\\"));
                _destfile = targetPath + file;
                System.IO.File.Copy(sourcePath, _destfile, false);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //Pass this image with SQL query to store it into
        //Database
        private void InsertImage(string mahinh, string tenhinh, string linkhinh, string enquirynumber)
        {
            try
            {
                if (_destfile != "")
                {
                    int i = 0;
                    string sql =
                        @"INSERT INTO HINHANH VALUES(@mahinh, @tenhinh, @linkhinh, @enquirynumber)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@mahinh", mahinh);
                    cmd.Parameters.AddWithValue("@tenhinh", tenhinh);
                    cmd.Parameters.AddWithValue("@linkhinh", linkhinh);
                    cmd.Parameters.AddWithValue("@enquirynumber", enquirynumber);
                    try
                    {
                        conn.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                    if (i > 0)
                    {
                        MessageBox.Show("Insert hình thành công");
                    }
                    else
                    {
                        MessageBox.Show("Thất bại upload hình");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}